package com.kaikeba.dao.impl;

import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoImpl implements BaseUserDao {

    static final String SQL_CONSOLE =" SELECT COUNT(ID) TOTAL_USER,COUNT(TO_DAYS(REGISTTIME)=TO_DAYS(NOW()) OR NULL) DAY_NUM FROM USERS";
    static final String SQL_FIND_ALL="SELECT * FROM USERS LIMIT ?,?";
    static final String SQL_FIND_BY_NUMBER ="SELECT * FROM USERS WHERE PHONENUMBER=?";
    static final String SQL_INSERT ="INSERT INTO USERS(ID,NUMBER,NICKNAME,PHONENUMBER,PASSWORD,REGISTTIME,LASTLOGINTIME) VALUES(?,?,?,?,?,NOW(),?)";
    static final String SQL_UPDATE ="UPDATE USERS SET NICKNAME =?,PHONENUMBER=?,PASSWORD=? WHERE PHONENUMBER=?";
    static final String SQL_DELETE ="DELETE FROM USERS WHERE PHONENUMBER=?";
    /**
     * 用与获取全部用户人数（总数 + 日新增）
     *
     * @return [{size:总数,day :新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Map<String,Integer>> list = new ArrayList<>();
        HashMap map = new HashMap();
        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String total_user = resultSet.getString("TOTAL_USER");
                String day_num = resultSet.getString("DAY_NUM");
                map.put("total_user",total_user);
                map.put("day_num",day_num);
                list.add(map);
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 用于查询所有用户
     *
     * @param limit      是否分页标记 true：分页
     * @param offset     SQL语句的起始索引
     * @param pageNumber 每一页查询的数量
     * @return
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            preparedStatement.setInt(1,offset);
            preparedStatement.setInt(2,pageNumber);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String nickName = resultSet.getString("nickName");
                String phoneNumber = resultSet.getString("phoneNumber");
                String password = resultSet.getString("password");
                Timestamp registTime = resultSet.getTimestamp("registTime");
                Timestamp lastLoginTime = resultSet.getTimestamp("lastLoginTime");
                User user = new User(id,number,nickName,phoneNumber,password,registTime,lastLoginTime);
                list.add(user);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return list;
    }

    /**
     * 根据用户手机号查询用户
     *
     * @param phoneNumber 手机号
     * @return 查询的用户信息，用户不存在时返回null
     */
    @Override
    public User findByNumber(String phoneNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            preparedStatement.setString(1,phoneNumber);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String nickName = resultSet.getString("nickName");
                String phoneNumber2 = resultSet.getString("phoneNumber");
                String password = resultSet.getString("password");
                Timestamp registTime = resultSet.getTimestamp("registTime");
                Timestamp lastLoginTime = resultSet.getTimestamp("lastLoginTime");
                user = new User(id,number,nickName,phoneNumber2,password,registTime,lastLoginTime);
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return user;
    }

    /**
     * 用户的录入
     *
     * @param u 用户对象
     * @return
     */
    @Override
    public boolean insert(User u) throws DuplicateCodeException {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        int i =0;
        try {
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            preparedStatement.setInt(1,u.getId());
            preparedStatement.setString(2,u.getNumber());
            preparedStatement.setString(3,u.getNickName());
            preparedStatement.setString(4,u.getPhoneNumber());
            preparedStatement.setString(5,u.getPassword());
//            preparedStatement.setTimestamp(6,u.getRegistTime());
            preparedStatement.setTimestamp(6,u.getLastLoginTime());
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return i>0?true:false;
    }

    /**
     * 快递修改
     *
     * @param phoneNumber      要修改的的id
     * @param newUser 新的对象（number,company,username,userPhone）
     * @return
     */
    @Override
    public boolean update(String phoneNumber, User newUser) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            preparedStatement.setString(1,newUser.getNickName());
            preparedStatement.setString(2,newUser.getPhoneNumber());
            preparedStatement.setString(3,newUser.getPassword());
            preparedStatement.setString(4,phoneNumber);
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return i>0?true:false;
    }

    /**
     * 删除
     *
     * @param userPhone 要删除
     * @return
     */
    @Override
    public boolean delete(String userPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        int i =0;
        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            preparedStatement.setString(1,userPhone);
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return i>0?true:false;
    }

    /**
     * @return
     */
    @Override
    public int TotalNumber() {

        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int number = 0;
        try {
            preparedStatement = connection.prepareStatement("select count(ID) number from users");
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                 number = resultSet.getInt("number");

            }else{
                return 0;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,null);
        }
        return number;
    }
}
